package com_.dxy_test_.easypoi_;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com_.dxy_test_.jdbc_.JdbcUtils;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Description:
 * @Author: ggf
 * @Date: 2020/06/13
 */
public class EasyPoiDemo {
    public static void main(String[] args) {
        String filePath = "C:\\Users\\Alex\\Desktop\\app\\园区风险评估指标表.xlsx";
        List<EvaluateInfo> evaluateInfoList = readExcel(filePath, 0, EvaluateInfo.class);
        insertDB(evaluateInfoList);//插入数据库
    }

    private static void insertDB(List<EvaluateInfo> evaluateInfoList) {
        if (evaluateInfoList == null || evaluateInfoList.size() == 0) {
            return;
        }

        //排序
        evaluateInfoList = evaluateInfoList.stream().sorted(Comparator.comparingInt(o -> Integer.parseInt(o.getId()))).collect(Collectors.toList());

        System.out.println("导入数据总行数为：" + evaluateInfoList.size());

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JdbcUtils.getConn();
            String sql = "insert into t_park_risk_evaluate_index(id,evaluate_type,evaluate_name,evaluate_content,evaluate_score,evaluate_detail_rule,evaluate_basis," +
                    "is_deleted,created_by,created_by_name,created_time) " +
                    "values(?,?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);

            for (EvaluateInfo info : evaluateInfoList) {
                insertRecord(ps, info);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, null);
        }
    }

    private static void insertRecord(PreparedStatement ps, EvaluateInfo info) {
        try {
            System.out.print("编号：" + info.getId() + "，");
            ps.setString(1, info.getId());
            ps.setString(2, info.getEvaluateType());
            ps.setString(3, info.getEvaluateName());
            ps.setString(4, info.getEvaluateContent());
            ps.setInt(5, info.getEvaluateScore());
            ps.setString(6, info.getEvaluateDetailRule());
            ps.setString(7, info.getEvaluateBasis());
            ps.setInt(8, 0);
            ps.setString(9, "admin");
            ps.setString(10, "系统管理员");
            ps.setTimestamp(11, new Timestamp(System.currentTimeMillis()));
            int row = ps.executeUpdate();
            if (row > 0) {
                System.out.println("插入成功！");
            }
            Thread.sleep(1000);//保证时间和id同序
        } catch (Exception e) {
            System.out.println("插入失败，失败原因为：" + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * @param filePath   Excel文件路径
     * @param sheetIndex 对应的表单，从0开始，0代表第一个表单
     * @param clazz      对应封装的数据实例对象
     * @return 返回数据集合
     */
    public static <E> List<E> readExcel(String filePath, int sheetIndex, Class<E> clazz) {
        // 定义输入流
        FileInputStream fis = null;
        List<E> datas = null;

        try {
            // 创建输入流对象
            fis = new FileInputStream(filePath);
            // 创建一个easypoi使用的配置类
            ImportParams params = new ImportParams();
            // 设置表格坐标
            params.setStartSheetIndex(sheetIndex);
            // 校验Excel文件，去掉空行
            params.setNeedVerify(true);
            // 读取数据
            datas = ExcelImportUtil.importExcel(fis, clazz, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return datas;
    }
}

